{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "file=\"表甲三.xlsx\"\n",
    "df01=pd.read_excel(file,sheet_name='Sheet1',header=1)\n",
    "df02=pd.read_excel(file,sheet_name='Sheet2',header=1)\n",
    "df03=pd.read_excel(file,sheet_name='Sheet3',header=1)\n",
    "df01 = df01.fillna(0)#将NAN值变成0\n",
    "df02 = df02.fillna(0)#将NAN值变成0\n",
    "df03 = df03.fillna(0)#将NAN值变成0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "table=pd.merge(df01,df02,on=['项目名称',\"编制依据\"],how=\"inner\",left_index=False).fillna('没找到')\n",
    "table2=pd.merge(table,df03,on=['项目名称',\"编制依据\"],how='inner',left_index=False).fillna('没找到')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号_x</th>\n",
       "      <th>编制依据</th>\n",
       "      <th>项目名称</th>\n",
       "      <th>单位_x</th>\n",
       "      <th>数量_x</th>\n",
       "      <th>设备单价_x</th>\n",
       "      <th>主要材料单价_x</th>\n",
       "      <th>单价定额基价_x</th>\n",
       "      <th>单价定额人工_x</th>\n",
       "      <th>设备合价_x</th>\n",
       "      <th>...</th>\n",
       "      <th>单位</th>\n",
       "      <th>数量</th>\n",
       "      <th>设备单价</th>\n",
       "      <th>主要材料单价</th>\n",
       "      <th>单价定额基价</th>\n",
       "      <th>单价定额人工</th>\n",
       "      <th>设备合价</th>\n",
       "      <th>主要材料合价</th>\n",
       "      <th>安装合价费用金额</th>\n",
       "      <th>其中人工</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>架空线路工程</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>255732.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>179296.0</td>\n",
       "      <td>2965084.0</td>\n",
       "      <td>2343122.0</td>\n",
       "      <td>482244.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>一</td>\n",
       "      <td>0</td>\n",
       "      <td>杆塔工程</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>255732.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2511210.0</td>\n",
       "      <td>1863804.0</td>\n",
       "      <td>380498.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>杆塔</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>255732.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2511210.0</td>\n",
       "      <td>1863804.0</td>\n",
       "      <td>380498.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.1</td>\n",
       "      <td>0</td>\n",
       "      <td>杆塔基础</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1230207.0</td>\n",
       "      <td>1635488.0</td>\n",
       "      <td>359154.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>JX1-17</td>\n",
       "      <td>人力运输 金具、绝缘子、零星钢材</td>\n",
       "      <td>t·km</td>\n",
       "      <td>5.35</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>97.38</td>\n",
       "      <td>88.85</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>t·km</td>\n",
       "      <td>5.363</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>97.38</td>\n",
       "      <td>88.85</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>522.0</td>\n",
       "      <td>477.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17524</th>\n",
       "      <td>0</td>\n",
       "      <td>C04400201</td>\n",
       "      <td>线路 联板</td>\n",
       "      <td>件</td>\n",
       "      <td>90.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>51.04</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>件</td>\n",
       "      <td>90.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>82.30</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7407.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17525</th>\n",
       "      <td>0</td>\n",
       "      <td>C04400204</td>\n",
       "      <td>线路 联板</td>\n",
       "      <td>件</td>\n",
       "      <td>72.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>72.89</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>件</td>\n",
       "      <td>72.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>63.72</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4588.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17526</th>\n",
       "      <td>0</td>\n",
       "      <td>C04520219</td>\n",
       "      <td>线路 直角挂板</td>\n",
       "      <td>件</td>\n",
       "      <td>162.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.81</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>件</td>\n",
       "      <td>162.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.66</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4158.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17527</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>金具</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8658.0</td>\n",
       "      <td>2033.0</td>\n",
       "      <td>363.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17528</th>\n",
       "      <td>2.3</td>\n",
       "      <td>0</td>\n",
       "      <td>保护金具</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8658.0</td>\n",
       "      <td>2033.0</td>\n",
       "      <td>363.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17529 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      序号_x       编制依据              项目名称  单位_x    数量_x  设备单价_x  主要材料单价_x  \\\n",
       "0        0          0            架空线路工程     0    0.00     0.0      0.00   \n",
       "1        一          0              杆塔工程     0    0.00     0.0      0.00   \n",
       "2        1          0                杆塔     0    0.00     0.0      0.00   \n",
       "3      1.1          0              杆塔基础     0    0.00     0.0      0.00   \n",
       "4        0     JX1-17  人力运输 金具、绝缘子、零星钢材  t·km    5.35     0.0      0.00   \n",
       "...    ...        ...               ...   ...     ...     ...       ...   \n",
       "17524    0  C04400201             线路 联板     件   90.00     0.0     51.04   \n",
       "17525    0  C04400204             线路 联板     件   72.00     0.0     72.89   \n",
       "17526    0  C04520219           线路 直角挂板     件  162.00     0.0     10.81   \n",
       "17527    2          0                金具     0    0.00     0.0      0.00   \n",
       "17528  2.3          0              保护金具     0    0.00     0.0      0.00   \n",
       "\n",
       "       单价定额基价_x  单价定额人工_x    设备合价_x  ...    单位       数量  设备单价 主要材料单价 单价定额基价  \\\n",
       "0          0.00      0.00  255732.0  ...     0    0.000   0.0   0.00   0.00   \n",
       "1          0.00      0.00  255732.0  ...     0    0.000   0.0   0.00   0.00   \n",
       "2          0.00      0.00  255732.0  ...     0    0.000   0.0   0.00   0.00   \n",
       "3          0.00      0.00       0.0  ...     0    0.000   0.0   0.00   0.00   \n",
       "4         97.38     88.85       0.0  ...  t·km    5.363   0.0   0.00  97.38   \n",
       "...         ...       ...       ...  ...   ...      ...   ...    ...    ...   \n",
       "17524      0.00      0.00       0.0  ...     件   90.000   0.0  82.30   0.00   \n",
       "17525      0.00      0.00       0.0  ...     件   72.000   0.0  63.72   0.00   \n",
       "17526      0.00      0.00       0.0  ...     件  162.000   0.0  25.66   0.00   \n",
       "17527      0.00      0.00       0.0  ...     0    0.000   0.0   0.00   0.00   \n",
       "17528      0.00      0.00       0.0  ...     0    0.000   0.0   0.00   0.00   \n",
       "\n",
       "       单价定额人工      设备合价     主要材料合价   安装合价费用金额      其中人工  \n",
       "0        0.00  179296.0  2965084.0  2343122.0  482244.0  \n",
       "1        0.00       0.0  2511210.0  1863804.0  380498.0  \n",
       "2        0.00       0.0  2511210.0  1863804.0  380498.0  \n",
       "3        0.00       0.0  1230207.0  1635488.0  359154.0  \n",
       "4       88.85       0.0        0.0      522.0     477.0  \n",
       "...       ...       ...        ...        ...       ...  \n",
       "17524    0.00       0.0     7407.0        0.0       0.0  \n",
       "17525    0.00       0.0     4588.0        0.0       0.0  \n",
       "17526    0.00       0.0     4158.0        0.0       0.0  \n",
       "17527    0.00       0.0     8658.0     2033.0     363.0  \n",
       "17528    0.00       0.0     8658.0     2033.0     363.0  \n",
       "\n",
       "[17529 rows x 35 columns]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(file, mode='a',engine='openpyxl') as writer:\n",
    "    table2.to_excel(writer,sheet_name='table2',index=False)  #新开一工作表I01而保存文件\n",
    "    table.to_excel(writer,sheet_name='table1',index=False)  #新开一工作表I01而保存文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "result=df01.join(df02,how='inner',lsuffix='_left', rsuffix='_right')\n",
    "result1=result.join(df03,how='inner',lsuffix='_left', rsuffix='_right')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号_left</th>\n",
       "      <th>编制依据_left</th>\n",
       "      <th>项目名称_left</th>\n",
       "      <th>单位_left</th>\n",
       "      <th>数量_left</th>\n",
       "      <th>设备单价_left</th>\n",
       "      <th>主要材料单价_left</th>\n",
       "      <th>单价定额基价_left</th>\n",
       "      <th>单价定额人工_left</th>\n",
       "      <th>设备合价_left</th>\n",
       "      <th>...</th>\n",
       "      <th>单位</th>\n",
       "      <th>数量</th>\n",
       "      <th>设备单价</th>\n",
       "      <th>主要材料单价</th>\n",
       "      <th>单价定额基价</th>\n",
       "      <th>单价定额人工</th>\n",
       "      <th>设备合价</th>\n",
       "      <th>主要材料合价</th>\n",
       "      <th>安装合价费用金额</th>\n",
       "      <th>其中人工</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>架空线路工程</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>255732.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>179296.0</td>\n",
       "      <td>2965084.0</td>\n",
       "      <td>2343122.0</td>\n",
       "      <td>482244.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>一</td>\n",
       "      <td>0</td>\n",
       "      <td>杆塔工程</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>255732.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2511210.0</td>\n",
       "      <td>1863804.0</td>\n",
       "      <td>380498.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>杆塔</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>255732.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2511210.0</td>\n",
       "      <td>1863804.0</td>\n",
       "      <td>380498.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.1</td>\n",
       "      <td>0</td>\n",
       "      <td>杆塔基础</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1230207.0</td>\n",
       "      <td>1635488.0</td>\n",
       "      <td>359154.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>JX1-17</td>\n",
       "      <td>人力运输 金具、绝缘子、零星钢材</td>\n",
       "      <td>t·km</td>\n",
       "      <td>5.350</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>97.38</td>\n",
       "      <td>88.85</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>t·km</td>\n",
       "      <td>5.363</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>97.38</td>\n",
       "      <td>88.85</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>522.0</td>\n",
       "      <td>477.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>415</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>金具</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>416</th>\n",
       "      <td>2.3</td>\n",
       "      <td>0</td>\n",
       "      <td>保护金具</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>%</td>\n",
       "      <td>9.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9808.26</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>883.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>417</th>\n",
       "      <td>0</td>\n",
       "      <td>JX1-17</td>\n",
       "      <td>人力运输 金具、绝缘子、零星钢材</td>\n",
       "      <td>t·km</td>\n",
       "      <td>0.005</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>97.38</td>\n",
       "      <td>88.85</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2033.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>418</th>\n",
       "      <td>0</td>\n",
       "      <td>JX1-101</td>\n",
       "      <td>汽车运输 金具、绝缘子、零星钢材 装卸</td>\n",
       "      <td>t</td>\n",
       "      <td>0.098</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36.26</td>\n",
       "      <td>9.47</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8658.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>419</th>\n",
       "      <td>0</td>\n",
       "      <td>JX1-102</td>\n",
       "      <td>汽车运输 金具、绝缘子、零星钢材 运输</td>\n",
       "      <td>t·km</td>\n",
       "      <td>0.977</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.36</td>\n",
       "      <td>0.37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10691.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>420 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    序号_left 编制依据_left            项目名称_left 单位_left  数量_left  设备单价_left  \\\n",
       "0         0         0               架空线路工程       0    0.000        0.0   \n",
       "1         一         0                 杆塔工程       0    0.000        0.0   \n",
       "2         1         0                   杆塔       0    0.000        0.0   \n",
       "3       1.1         0                 杆塔基础       0    0.000        0.0   \n",
       "4         0    JX1-17     人力运输 金具、绝缘子、零星钢材    t·km    5.350        0.0   \n",
       "..      ...       ...                  ...     ...      ...        ...   \n",
       "415       2         0                   金具       0    0.000        0.0   \n",
       "416     2.3         0                 保护金具       0    0.000        0.0   \n",
       "417       0    JX1-17     人力运输 金具、绝缘子、零星钢材    t·km    0.005        0.0   \n",
       "418       0   JX1-101  汽车运输 金具、绝缘子、零星钢材 装卸       t    0.098        0.0   \n",
       "419       0   JX1-102  汽车运输 金具、绝缘子、零星钢材 运输    t·km    0.977        0.0   \n",
       "\n",
       "     主要材料单价_left  单价定额基价_left  单价定额人工_left  设备合价_left  ...    单位     数量  设备单价  \\\n",
       "0            0.0         0.00         0.00   255732.0  ...     0  0.000   0.0   \n",
       "1            0.0         0.00         0.00   255732.0  ...     0  0.000   0.0   \n",
       "2            0.0         0.00         0.00   255732.0  ...     0  0.000   0.0   \n",
       "3            0.0         0.00         0.00        0.0  ...     0  0.000   0.0   \n",
       "4            0.0        97.38        88.85        0.0  ...  t·km  5.363   0.0   \n",
       "..           ...          ...          ...        ...  ...   ...    ...   ...   \n",
       "415          0.0         0.00         0.00        0.0  ...     0  0.000   0.0   \n",
       "416          0.0         0.00         0.00        0.0  ...     %  9.000   0.0   \n",
       "417          0.0        97.38        88.85        0.0  ...     0  0.000   0.0   \n",
       "418          0.0        36.26         9.47        0.0  ...     0  0.000   0.0   \n",
       "419          0.0         1.36         0.37        0.0  ...     0  0.000   0.0   \n",
       "\n",
       "    主要材料单价   单价定额基价 单价定额人工      设备合价     主要材料合价   安装合价费用金额      其中人工  \n",
       "0      0.0     0.00   0.00  179296.0  2965084.0  2343122.0  482244.0  \n",
       "1      0.0     0.00   0.00       0.0  2511210.0  1863804.0  380498.0  \n",
       "2      0.0     0.00   0.00       0.0  2511210.0  1863804.0  380498.0  \n",
       "3      0.0     0.00   0.00       0.0  1230207.0  1635488.0  359154.0  \n",
       "4      0.0    97.38  88.85       0.0        0.0      522.0     477.0  \n",
       "..     ...      ...    ...       ...        ...        ...       ...  \n",
       "415    0.0     0.00   0.00       0.0        0.0        3.0       0.0  \n",
       "416    0.0  9808.26   0.00       0.0        0.0      883.0       0.0  \n",
       "417    0.0     0.00   0.00       0.0        0.0     2033.0       0.0  \n",
       "418    0.0     0.00   0.00       0.0        0.0     8658.0       0.0  \n",
       "419    0.0     0.00   0.00       0.0        0.0    10691.0       0.0  \n",
       "\n",
       "[420 rows x 39 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(file, mode='a',engine='openpyxl') as writer:\n",
    "    result1.to_excel(writer,sheet_name='result1',index=False)  #新开一工作表I01而保存文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>编制依据</th>\n",
       "      <th>项目名称</th>\n",
       "      <th>单位</th>\n",
       "      <th>数量</th>\n",
       "      <th>设备单价</th>\n",
       "      <th>主要材料单价</th>\n",
       "      <th>单价定额基价</th>\n",
       "      <th>单价定额人工</th>\n",
       "      <th>设备合价</th>\n",
       "      <th>...</th>\n",
       "      <th>单位</th>\n",
       "      <th>数量</th>\n",
       "      <th>设备单价</th>\n",
       "      <th>主要材料单价</th>\n",
       "      <th>单价定额基价</th>\n",
       "      <th>单价定额人工</th>\n",
       "      <th>设备合价</th>\n",
       "      <th>主要材料合价</th>\n",
       "      <th>安装合价费用金额</th>\n",
       "      <th>其中人工</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>架空线路工程</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>255732.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>179296</td>\n",
       "      <td>2.96508e+06</td>\n",
       "      <td>2.34312e+06</td>\n",
       "      <td>482244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>一</td>\n",
       "      <td>0</td>\n",
       "      <td>杆塔工程</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>255732.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.51121e+06</td>\n",
       "      <td>1.8638e+06</td>\n",
       "      <td>380498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>杆塔</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>255732.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.51121e+06</td>\n",
       "      <td>1.8638e+06</td>\n",
       "      <td>380498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.1</td>\n",
       "      <td>0</td>\n",
       "      <td>杆塔基础</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.23021e+06</td>\n",
       "      <td>1.63549e+06</td>\n",
       "      <td>359154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>JX1-17</td>\n",
       "      <td>人力运输 金具、绝缘子、零星钢材</td>\n",
       "      <td>t·km</td>\n",
       "      <td>5.35</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>97.38</td>\n",
       "      <td>88.85</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>t·km</td>\n",
       "      <td>5.363</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>97.38</td>\n",
       "      <td>88.85</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>522</td>\n",
       "      <td>477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>451</th>\n",
       "      <td>0</td>\n",
       "      <td>4.2</td>\n",
       "      <td>乙供主要材料价差</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>452</th>\n",
       "      <td>0</td>\n",
       "      <td>五</td>\n",
       "      <td>税金</td>\n",
       "      <td>%</td>\n",
       "      <td>9.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2176.90</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>453</th>\n",
       "      <td>0</td>\n",
       "      <td>六</td>\n",
       "      <td>安装费</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454</th>\n",
       "      <td>0</td>\n",
       "      <td>七</td>\n",
       "      <td>主材费</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455</th>\n",
       "      <td>0</td>\n",
       "      <td>八</td>\n",
       "      <td>合计</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "      <td>没找到</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>456 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      序号    编制依据              项目名称    单位    数量  设备单价 主要材料单价   单价定额基价 单价定额人工  \\\n",
       "0      0       0            架空线路工程     0  0.00   0.0    0.0     0.00   0.00   \n",
       "1      一       0              杆塔工程     0  0.00   0.0    0.0     0.00   0.00   \n",
       "2      1       0                杆塔     0  0.00   0.0    0.0     0.00   0.00   \n",
       "3    1.1       0              杆塔基础     0  0.00   0.0    0.0     0.00   0.00   \n",
       "4      0  JX1-17  人力运输 金具、绝缘子、零星钢材  t·km  5.35   0.0    0.0    97.38  88.85   \n",
       "..   ...     ...               ...   ...   ...   ...    ...      ...    ...   \n",
       "451    0     4.2          乙供主要材料价差     0  0.00   0.0    0.0     0.00   0.00   \n",
       "452    0       五                税金     %  9.00   0.0    0.0  2176.90   0.00   \n",
       "453    0       六               安装费     0  0.00   0.0    0.0     0.00   0.00   \n",
       "454    0       七               主材费     0  0.00   0.0    0.0     0.00   0.00   \n",
       "455    0       八                合计     0  0.00   0.0    0.0     0.00   0.00   \n",
       "\n",
       "         设备合价  ...    单位     数量  设备单价 主要材料单价 单价定额基价 单价定额人工    设备合价  \\\n",
       "0    255732.0  ...     0      0     0      0      0      0  179296   \n",
       "1    255732.0  ...     0      0     0      0      0      0       0   \n",
       "2    255732.0  ...     0      0     0      0      0      0       0   \n",
       "3         0.0  ...     0      0     0      0      0      0       0   \n",
       "4         0.0  ...  t·km  5.363     0      0  97.38  88.85       0   \n",
       "..        ...  ...   ...    ...   ...    ...    ...    ...     ...   \n",
       "451       0.0  ...   没找到    没找到   没找到    没找到    没找到    没找到     没找到   \n",
       "452       0.0  ...   没找到    没找到   没找到    没找到    没找到    没找到     没找到   \n",
       "453       0.0  ...   没找到    没找到   没找到    没找到    没找到    没找到     没找到   \n",
       "454       0.0  ...   没找到    没找到   没找到    没找到    没找到    没找到     没找到   \n",
       "455       0.0  ...   没找到    没找到   没找到    没找到    没找到    没找到     没找到   \n",
       "\n",
       "          主要材料合价     安装合价费用金额    其中人工  \n",
       "0    2.96508e+06  2.34312e+06  482244  \n",
       "1    2.51121e+06   1.8638e+06  380498  \n",
       "2    2.51121e+06   1.8638e+06  380498  \n",
       "3    1.23021e+06  1.63549e+06  359154  \n",
       "4              0          522     477  \n",
       "..           ...          ...     ...  \n",
       "451          没找到          没找到     没找到  \n",
       "452          没找到          没找到     没找到  \n",
       "453          没找到          没找到     没找到  \n",
       "454          没找到          没找到     没找到  \n",
       "455          没找到          没找到     没找到  \n",
       "\n",
       "[456 rows x 39 columns]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table3 = pd.concat([df01,df02],axis = 1,ignore_index=False).fillna('没找到')\n",
    "table4=pd.concat([table3,df03],axis=1).fillna('没找到')\n",
    "table4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(file, mode='a',engine='openpyxl') as writer:\n",
    "    table4.to_excel(writer,sheet_name='三表合并',index=False)  #新开一工作表I01而保存文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
